import datetime
from other.cfg import Config
from other.change_format import change_format
from other.sql_montage import sqlMontage
from other.status import Status
from server.Mysql import MySQL


class role:
    def __init__(self):
        self.cfg = Config()
        self.st = Status()
        self.db = MySQL(self.cfg.res['mysql']['database'], 'sys_role')
        self.cf = change_format()
        self.sm = sqlMontage()

    # 获取角色
    def get_role(self, data):
        result = {'msg': '成功', 'code': 200, 'data': []}
        sqls = f"where `del_flag`='{self.st.get_status('delFlag', '存在')}' "
        sql = ''
        if data != {}:
            if data.get('roleName') is not None:
                if data['roleName'] != '':
                    sql = sql + " and `role_name` like '%{}%'".format(data['roleName'])
            if data.get('roleKey') is not None:
                if data['roleKey'] != '':
                    sql = sql + " and `role_key`='{}'".format(data['roleKey'])
            if data.get('status') is not None:
                if data['status'] != '':
                    sql = sql + " and `status`='{}'".format(data['status'])
            if data.get('beginTime') is not None and data.get('endTime') is not None:
                if data['beginTime'] != '' and data['endTime'] != '':
                    sql = (sql + " and ('{}'<=`create_time` and `create_time`<='{}')".
                           format(data['beginTime'], data['endTime']))
        if sql != "":
            sql = sqls + sql
        else:
            sql = sqls
        res = self.db.get(sql)
        if res != ():
            for r in res:
                menuIds = []
                for key, value in r.items():
                    if isinstance(value, datetime.datetime):
                        r[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                # 获取角色菜单信息
                me_sql = f"select * from `sys_role_menu` where `role_id`={r['role_id']}"
                me_res = self.db.gets(me_sql)
                if me_res != ():
                    for me in me_res:
                        menuIds.append(me['menu_id'])
                r['menuIds'] = menuIds
                result['data'].append(r)
        result = self.cf.changes_format(result)
        return result

    # 角色获取用户
    def get_role_user(self, data):
        result = {'msg': '成功', 'code': 200, 'data': [], 'total': None}
        sqls = f"select su.`user_id`,su.`user_name`,su.`nick_name`,su.`email`,su.`phone_number`,su.`status`,su.`create_time` from `sys_user_role` sur join `sys_user` su on sur.`user_id`=su.`user_id` and su.`del_flag`='{self.st.get_status('delFlag', '存在')}' and su.`user_name` !='admin' where 1=1 "
        sql = ''
        pages = ''
        if data != {}:
            if data.get('pageNum') is not None and data.get('pageSize') is not None:
                if data['pageNum'] != '' and data['pageSize'] != '':
                    pages = " limit {},{}".format(int(data['pageNum']) * int(data['pageSize']) - int(data['pageSize']),
                                                  int(data['pageSize']))
            if data.get('roleId') is not None:
                if data['roleId'] != '':
                    sql = sql + " and sur.`role_id`='{}'".format(data['roleId'])
            if data.get('userName') is not None:
                if data['userName'] != '':
                    sql = sql + " and su.`nick_name` like '%{}%'".format(data['userName'])
            if data.get('phoneNumber') is not None:
                if data['phoneNumber'] != '':
                    sql = sql + " and su.`phone_number`='{}'".format(data['phoneNumber'])
        total = self.db.gets(sqls + sql)
        result['total'] = len(total)
        if sql != "":
            sql = sqls + sql + f'{pages}'
        else:
            sql = sqls + f'{pages}'
        res = self.db.gets(sql)
        if res != ():
            for r in res:
                for key, value in r.items():
                    if isinstance(value, datetime.datetime):
                        r[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                result['data'].append(r)
        result = self.cf.changes_format(result)
        return result

    # 角色获取用户
    def get_role_unallocatedList(self, data):
        result = {'msg': '成功', 'code': 200, 'data': [], 'total': None}
        g_sql = f"select * from `sys_user_role` where `role_id`={data['roleId']} "
        g_res = self.db.gets(g_sql)
        users = []
        if g_res != ():
            for g in g_res:
                users.append(g['user_id'])
        users = str(users).replace('[', '(').replace(']', ')')
        sqls = f"select `user_id`,`user_name`,`nick_name`,`email`,`phone_number`,`status`,`create_time` from `sys_user` where `del_flag`='{self.st.get_status('delFlag', '存在')}' and `user_name` !='admin' and `status`='{self.st.get_status('status', '启用')}' and `user_id` NOT IN {users} "
        sql = ''
        pages = ''
        if data != {}:
            if data.get('pageNum') is not None and data.get('pageSize') is not None:
                if data['pageNum'] != '' and data['pageSize'] != '':
                    pages = " limit {},{}".format(
                        int(data['pageNum']) * int(data['pageSize']) - int(data['pageSize']),
                        int(data['pageSize']))
                if data.get('userName') is not None:
                    if data['userName'] != '':
                        sql = sql + " and `nick_name` like '%{}%'".format(data['userName'])
                if data.get('phoneNumber') is not None:
                    if data['phoneNumber'] != '':
                        sql = sql + " and `phone_number`='{}'".format(data['phoneNumber'])
        total = self.db.gets(sqls + sql)
        result['total'] = len(total)
        if sql != "":
            sql = sqls + sql + f'{pages}'
        else:
            sql = sqls + f'{pages}'
        res = self.db.gets(sql)
        if res != ():
            for r in res:
                for key, value in r.items():
                    if isinstance(value, datetime.datetime):
                        r[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                result['data'].append(r)
        result = self.cf.changes_format(result)
        return result

    # 添加角色
    def role_add(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        g_sql = f"where (`role_key`='{data['roleKey']}' or `role_name`='{data['roleName']}') and `del_flag`='{self.st.get_status('delFlag', '存在')}'"
        g_res = self.db.get(g_sql)
        if g_res != ():
            for g in g_res:
                if g['role_key'] == data['roleKey']:
                    result['msg'] = '角色权限字符串重复'
                if g['role_name'] == data['roleName']:
                    result['msg'] = '角色名称重复'
            result['code'] = 500
        else:
            data['create_by'] = userInfo['userName']
            data['create_time'] = times
            add_sql = self.sm.add_sql_data(data, 'sys_role')
            # print('add_sql', add_sql)
            role_id = self.db.adds(add_sql, 'sys_role')
            if data['menuIds']:
                for menuId in data['menuIds']:
                    dd = {'role_id': role_id, 'menu_id': menuId}
                    add_mn_sql = self.sm.add_sql_data(dd, 'sys_role_menu')
                    # print('add_mn_sql', add_mn_sql)
                    self.db.adds(add_mn_sql, 'sys_role_menu')
        return result

    # 修改角色
    def role_edit(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        g_sql = f"where (`role_key`='{data['roleKey']}' or `role_name`='{data['roleName']}') and `del_flag`='{self.st.get_status('delFlag', '存在')}'"
        g_res = self.db.get(g_sql)
        ff = False
        # print('g_res', g_res)
        if g_res != ():
            for g in g_res:
                if g['role_id'] == data['roleId']:
                    ff = True
                else:
                    if g['role_key'] == data['roleKey']:
                        result['msg'] = '角色权限字符串重复'
                    if g['role_name'] == data['roleName']:
                        result['msg'] = '角色名称重复'
                    ff = False
                    result['code'] = 500
                    break
        else:
            ff = True
        if ff:
            # e_sql = f"`role_name`='{data['roleName']}',`role_key`='{data['roleKey']}',`role_sort`={data['roleSort']},`status`='{data['status']}',`update_by`='{userInfo['userName']}',`update_time`='{times}',`remark`='{data['remark']}' where `role_id`={data['roleId']}"
            # self.db.edit(e_sql)
            if 'createTime' in data:
                del data['createTime']
            data['update_by'] = userInfo['userName']
            data['update_time'] = times
            edit_sql = self.sm.edit_sql_data(data, 'sys_role', 'role_id', data['roleId'])
            # print('edit_sql', edit_sql)
            self.db.edits(edit_sql, 'sys_role')

            # 删除所有权限
            d_sql = f"delete from `sys_role_menu` where `role_id`={data['roleId']}"
            self.db.edits(d_sql, 'sys_role_menu')
            if data['menuIds']:
                for menuId in data['menuIds']:
                    dd = {'role_id': data['roleId'], 'menu_id': menuId}
                    add_mn_sql = self.sm.add_sql_data(dd, 'sys_role_menu')
                    # print('add_mn_sql', add_mn_sql)
                    self.db.edits(add_mn_sql, 'sys_role_menu')
                    # add_mn_sql = f"insert into `sys_role_menu` values ({data['roleId']},{menuId})"
                    # self.db.edits(add_mn_sql, 'sys_role_menu')
        return result

    # 删除角色
    def role_del(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        erros = ''
        ids = [int(x) for x in data.split(',')]
        for i in ids:
            u_sql = f"select sur.*,sr.`role_name` from `sys_user_role` sur join `sys_role` sr on sur.`role_id`=sr.`role_id` and sr.`del_flag`='{self.st.get_status('delFlag', '存在')}' where sur.`role_id`={i}"
            u_res = self.db.gets(u_sql)
            if u_res != ():
                for u in u_res:
                    erros = ','.join([u['role_name']])
            else:
                e_sql = f"`del_flag`='{self.st.get_status('delFlag', '已删除')}',`update_by`='{userInfo['userName']}',`update_time`='{times}' where `role_id`={i}"
                self.db.edit(e_sql)
                # 删除关联关系
                d_sql = f"delete from `sys_role_menu` where `role_id`={i}"
                self.db.edits(d_sql, 'sys_role_menu')
        if erros != '':
            # print(erros)
            result['msg'] = erros + '，删除失败'
        return result

    # 修改用户角色
    def edit_user_role(self, data):
        result = {'msg': '成功', 'code': 200}
        if data['userIds']:
            userIds = data['userIds'].split(',')
            for rr in userIds:
                dd = {'user_id': rr, 'role_id': data['roleId']}
                add_role_sql = self.sm.add_sql_data(dd, 'sys_user_role')
                self.db.adds(add_role_sql, 'sys_user_role')
        return result

    # 删除用户角色
    def del_user_role(self, data):
        result = {'msg': '成功', 'code': 200}
        if data['userIds']:
            userIds = data['userIds'].split(',')
            for rr in userIds:
                # 删除所有权限
                d_sql = f"delete from `sys_user_role` where `user_id`={rr} and `role_id`={data['roleId']}"
                self.db.edits(d_sql, 'sys_user_role')
        return result
